package org.zzh.parse.test;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import org.apache.poi.hwpf.HWPFDocument;
import org.apache.poi.hwpf.usermodel.Range;

/**
 * CvToWord.java
 * 
 * @author zhangzhenhua
 * @date 2016年10月11日 下午3:35:15
 * @description 简历导入到模板
 */
public class CvToWord {

	/**
	 * 用一个doc文档作为模板，然后替换其中的内容，再写入目标文档中。 换行正常
	 * 
	 * @throws Exception
	 */
	/* cv_basic表 */
	public static int id;
	public static int candidateid;
	public static Date create_time;
	public static Date update_time;
	public static int process_stage;
	public static String name;
	public static String usedname;
	public static String englishname;
	public static int gender;
	public static int cardtype;
	public static String idcard;
	public static String company;
	public static String title;
	public static String city;
	public static String birth_place;
	public static String phone;
	public static String mobile;
	public static String email;
	public static String age;
	public static String birthday;
	public static int marriage;
	public static String major;
	public static int degree;
	public static String school;
	public static String jobtime;
	public static String height;
	public static String weight;
	public static String BWH;
	public static String nationality;
	public static String country;
	public static String health;
	public static String politics;
	public static int job_status;
	public static String current_salary;
	public static int expected_salary;
	public static String expected_city;
	public static String expected_job;
	public static String weixin;
	public static String qq;
	public static String linkedin;
	public static String dajie;
	public static String weibo;
	public static float score;
	public static String basic_info;
	public static String address;
	public static int trade_id;
	public static int function_id;
	public static String mpc;
	public static String photo_id;
	public static String comment;
	public static String preference;
	public static int flag;
	public static int fileid;
	public static int ownerid;
	public static int company_id;
	public static int job_time;

	/* cv_education表 */

	// public static int id;
	// public static Date create_time;
	// public static Date update_time;
	// public static int candidateid;
	// public static int process_stage;
	public static String start_time;
	public static String end_time;
	// public static String school;
	// public static String major;
	// public static int degree;
	public static String info;
	// public static float score;
	public static String education_info;
	// public static int flag;
	// public static int fileid;

	/* cv_job表 */

	// private int id;
	// private Date create_time;
	// private Date update_time;
	// private int candidateid;
	// private int process_stage;
	// private String start_time;
	// private String end_time;
	// private String company;
	private static String department;
	// private String title;
	private static String report;
	private static String salary;
	private static String member;
	private static String duty;
	private static String achieve;
	private static String reason;
	// private String city;
	// private String info;
	// private float score;
	private static String job_info;
	// private int flag;
	// private int fileid;

	/* cv_project表 */

	// private int id;
	// private Date create_time;
	// private Date update_time;
	// private int candidateid;
	// private int process_stage;
	// private String start_time;
	// private String end_time;
	private static String project_name;
	// private String company;
	private static String content;
	// private String duty;
	// private String achieve;
	// private String city;
	// private String info;
	// private float score;
	private static String project_info;
	// private int flag;

	/* cv_skill表 */

	// private int id;
	// private Date create_time;
	// private Date update_time;
	// private int candidateid;
	// private int process_stage;
	private static String skill_name;
	// private String info;
	// private float score;
	private static String skill_info;
	// private int flag;
	private static int skill_experience;

	/* cv_commment表 --略 */

	public static Connection con;
	public static PreparedStatement ps;
	public static ResultSet rs;
	public static String driver = "com.mysql.jdbc.Driver";
	public static String url = "jdbc:mysql://123.57.35.10:3306/hunter2?zeroDateTimeBehavior=convertToNull&useSSL=false";
	public static String user = "root";
	public static String password = "hunter";

	public static void main(String[] args) {

		try {
			model1();

		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	public static Connection getConnection() {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return con;
	}

 
	public static void newLine() throws Exception {
		String templatePath = "D:\\zzh_file\\replaceModel.doc";
		InputStream is = new FileInputStream(templatePath);
		HWPFDocument doc = new HWPFDocument(is);
		Range range = doc.getRange();
		// 在表格内使用“\r”是不能换行的
		range.replaceText("${reportDate}", "2014-02-28");
		// (char)11就代表一个换行符可以用在表格中
		range.replaceText("${reportContent}", "参数2的内容" + (char) 11 + "换行");
		// 非表格内使用“\r”是可以换行的
		range.replaceText("${appleAmt}", "参数3的内容\r换行");
		// 非表格内使用“(char)11”也是可以换行的
		range.replaceText("${bananaAmt}", "参数4的内容" + (char) 11 + "换行");
		range.replaceText("${totalAmt}", "参数5的内容" + (char) 11 + "换行");
		OutputStream os = new FileOutputStream("D:\\zzh_file\\test1.doc");
		doc.write(os);
		os.close();
		is.close();
		System.out.println("ok");
	}

	/* 生成模板 */
	public static void model1() throws IOException {
		int id = 49707564;
		String sql_cv_basic = "select *from cv_basic where candidateid = "+id;
		String sql_cv_education = "select *from cv_education where candidateid = "+id;
		String sql_cv_job = "select *from cv_job where candidateid = "+id;
		String sql_cv_project = "select *from cv_project where candidateid = "+id;
		String sql_cv_skill = "select *from cv_skill where candidateid = "+id;
		con = getConnection();
		String templatePath = "D:\\zzh_file\\model.doc";
		InputStream is = new FileInputStream(templatePath);
		HWPFDocument doc = new HWPFDocument(is);
		Range range = doc.getRange();
		try {
			/*基本信息*/ 
			ps = con.prepareStatement(sql_cv_basic);
			rs = ps.executeQuery();
			while (rs.next()) {
				name = changeNull(rs.getString("name"));
				gender = rs.getInt("gender");
				birthday = changeNull(rs.getString("birthday"));
				birth_place = changeNull(rs.getString("birth_place"));
				city = changeNull(rs.getString("city"));
				address = changeNull(rs.getString("address"));
				current_salary = changeNull(rs.getString("current_salary"));
				expected_salary = rs.getInt("expected_salary");// 待转化
				comment = changeNull(rs.getString("comment"));
			}

			range.replaceText("${name}", name);
			range.replaceText("${gender}", getGender(gender));
			range.replaceText("${birthday}", birthday);
			range.replaceText("${birth_place}", birth_place);
			range.replaceText("${city}", city);
			range.replaceText("${address}", address);
			range.replaceText("${current_salary}", current_salary);
			range.replaceText("${expected_salary}", getExpectedSallary(expected_salary));
			
			
			
			/* 教育经历 */
			ps = con.prepareStatement(sql_cv_education);
			rs = ps.executeQuery();
			String line = "";
			while (rs.next()) {
				start_time = changeNull(rs.getString("start_time"));
				end_time = changeNull(rs.getString("end_time"));
				school = changeNull(rs.getString("school"));
				major = changeNull(rs.getString("major"));
				degree = rs.getInt("degree");
				education_info = changeNull(rs.getString("education_info"));//

				line += start_time + " - " + end_time + "\t\t" + school + "\t\t" + major + "\t\t" + getDegree(degree)
						+ (char) 11 ;
				

			} 
			if(!line.trim().equals(""))
				range.replaceText("${education}", line);
			else
				range.replaceText("${education}", "\t暂无");
			
			/*推荐说明*/ 
			range.replaceText("${recommendInfo}", "\t暂无");
			
			
			
			/* 工作经历 */
			ps = con.prepareStatement(sql_cv_job);
			rs = ps.executeQuery();
			line = "";
			while (rs.next()) {
				start_time = changeNull(rs.getString("start_time"));
				end_time = changeNull(rs.getString("end_time"));
				 
				company = changeNull(rs.getString("company"));
				department = changeNull(rs.getString("department"));
				title = changeNull( rs.getString("title"));
				report = changeNull(rs.getString("report"));
				salary = changeNull(rs.getString("salary"));//
				member = changeNull(rs.getString("member"));
				duty = changeNull(rs.getString("duty"));
				achieve = changeNull(rs.getString("achieve"));//
				city = changeNull(rs.getString("city"));
				
				line += start_time + " - " + end_time + "\t\t\t" + company
						+(char)11+"职位：\t"+title
						+(char)11+"汇报对象：\t"+report
						+(char)11+"下属人数：\t"+member
						+(char)11+"所在部门：\t"+department
						+(char)11+"所在地区：\t"+city
						+(char)11+"工作职责：\t"+duty
						+(char)11+(char)11;
				 
			} 
			if(!line.trim().equals(""))
				range.replaceText("${jobExperience}", line);
			else
				range.replaceText("${jobExperience}", "\t暂无");
			 
			/* 项目经历 */
			ps = con.prepareStatement(sql_cv_project);
			rs = ps.executeQuery();
			line = "";
			while (rs.next()) {
				start_time = changeNull(rs.getString("start_time"));
				end_time = changeNull(rs.getString("end_time"));
				project_name = changeNull(rs.getString("project_name"));
				company = changeNull(rs.getString("company"));
				content = changeNull(rs.getString("content"));
				duty = changeNull(rs.getString("duty"));
				achieve = changeNull(rs.getString("achieve"));//
				city = changeNull(rs.getString("city"));
				project_info = changeNull(rs.getString("project_info")); 
				 
				
				
				line += start_time + " - " + end_time + "\t\t\t" + project_name
						+(char)11+"公司：\t\t"+company
						+(char)11+"项目内容：\t"+content
						+(char)11+"项目职责：\t"+duty
						+(char)11+"项目成就：\t"+achieve
						+(char)11+"所在地区：\t"+city
						+(char)11+"其他信息：\t"+project_info
						+(char)11+(char)11;

			}
			if(!line.trim().equals(""))
				range.replaceText("${projectExperience}", line);
			else
				range.replaceText("${projectExperience}", "\t暂无");
			
			/* 个人技能 */
			ps = con.prepareStatement(sql_cv_skill);
			rs = ps.executeQuery();
			line = "";
			while (rs.next()) {
				skill_name = changeNull(rs.getString("skill_name"));//技能名
				//info = rs.getString("info");//
				skill_info = changeNull(rs.getString("skill_info"));//熟练程度
				skill_experience = rs.getInt("skill_experience"); //技能用时
				 
				
				
				line += skill_name + "\t\t\t" + skill_info + "\t\t\t" + skill_experience 
						+(char)11;
			} 
			if(!line.trim().equals(""))
				range.replaceText("${skill}", line);
			else
				range.replaceText("${skill}", "\t暂无");
			/* 自我评价 */
			 
			 
			line="";
				line += "\t"+comment 
						+(char)11; 
				if(!line.trim().equals(""))
					range.replaceText("${comment}", line);
				else
					range.replaceText("${comment}", "\t暂无");
 
			
			rs.close();
			ps.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		OutputStream os = new FileOutputStream("D:\\zzh_file\\test1.doc");
		doc.write(os);
		os.close();
		is.close();
		System.out.println("ok");

	}

	/* 属性还原 */

	public static String getGender(int i) {
		if (1 == i) {
			return "男";
		} else if (2 == i) {
			return "女";
		}
		return "保密";
	}

	public static String getExpectedSallary(int sallary) {
		if (67 == sallary) {
			return "5万以下";
		} else if (68 == sallary) {
			return "5-10万";
		} else if (69 == sallary) {
			return "10-20万";
		} else if (70 == sallary) {
			return "20-30万";
		} else if (71 == sallary) {
			return "30-50万";
		} else if (72 == sallary) {
			return "50-100万";
		} else if (73 == sallary) {
			return "100万以上";
		} else if (74 == sallary) {
			return "300-500万";
		} else if (75 == sallary) {
			return "500万以上";
		}

		return "未知";
	}

	public static String getDegree(int degree) {
		if (3 == degree) {
			return "专科及以下";
		} else if (4 == degree) {
			return "本科";
		} else if (5 == degree) {
			return "硕士";
		} else if (6 == degree) {
			return "博士";
		} else if (7 == degree) {
			return "博士后";
		}

		return "";
	} 
	
	public static String changeNull(String str) {
		if (str == null)
			return "";
		return str;
	}

}